﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using Model;
using DbCommon;
namespace DAL
{
    public class PercentDAL
    {
        DbHelper dbhelper = new DbHelper();
         /// <summary>
        /// 根据模型来更新数据
        /// </summary>
        /// <param name="model">百分比实体</param>
        /// <returns>返回受影响的行数</returns>
        public int update(BoPercent model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Persent] SET ");
            sql.Append("PER_CONTEST=@PER_CONTEST");
            sql.Append(",PER_AWARDS=@PER_AWARDS");
            sql.Append(",PER_PERSENT=@PER_PERSENT");
            sql.Append(",PER_ORDER=@PER_ORDER");
            sql.Append(" where PER_ID=@PER_ID");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }
        /// <summary>
        /// 根据实体来插入一条数据
        /// </summary>
        /// <param name="model">实体</param>
        /// <returns>返回受影响的行数</returns>
        public int insert(BoPercent model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Persent]");
            sql.Append("([PER_ID],[PER_CONTEST],[PER_AWARDS],[PER_PERSENT],[PER_ORDER])");
            sql.Append("VALUES");
            sql.Append("(@PER_ID,@PER_CONTEST,@PER_AWARDS,@PER_PERSENT,@PER_ORDER)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 根据实体对象来转化参数数组
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoPercent model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@PER_ID",SqlDbType.NVarChar,50),
                new SqlParameter("@PER_CONTEST",SqlDbType.NVarChar,50),
                new SqlParameter("@PER_AWARDS",SqlDbType.NVarChar,50),
                new SqlParameter("@PER_PERSENT",SqlDbType.Int),
                new SqlParameter("@PER_ORDER",SqlDbType.Int),
            };
            parameters[0].Value = model.PER_ID;
            parameters[1].Value = model.PER_CONTEST;
            parameters[2].Value = model.PER_AWARDS;
            parameters[3].Value = model.PER_PERSENT;
            parameters[4].Value = model.PER_ORDER;
            return parameters;
        }

        /// <summary>
        /// 根据条件来获取视图列表
        /// </summary>
        /// <param name="ConStr">条件</param>
        /// <returns>返回相应的对象列表</returns>
        public IList<BoPercent> Getv_Participant(string ConStr)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from V_Percent where 1=1 ");
            if (!string.IsNullOrEmpty(ConStr))
            {
                sql.Append(" and ");
                sql.Append(ConStr);
            }
            sql.Append(" ORDER BY PER_ORDER ");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            IList<BoPercent> list = GetVList(table);
            return list != null && list.Count > 0 ? list : null;
        }

        /// <summary>
        /// 将table转化成相应的list对象表格
        /// </summary>
        /// <param name="table">table/param>
        /// <returns>返回数据列表</returns>
        public IList<BoPercent> GetList(DataTable table)
        {
            List<BoPercent> list = new List<BoPercent>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoPercent model = new BoPercent();
                    model.PER_ID = (string)table.Rows[i]["PER_ID"];
                    model.PER_ORDER = (int)table.Rows[i]["PER_ORDER"];
                    model.PER_CONTEST = table.Rows[i]["PER_CONTEST"] == System.DBNull.Value ? "" : (string)table.Rows[i]["PER_CONTEST"];
                    model.PER_AWARDS = table.Rows[i]["PER_AWARDS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["PER_AWARDS"];
                    model.PER_PERSENT = table.Rows[i]["PER_PERSENT"] == System.DBNull.Value ? 0 : (int)table.Rows[i]["PER_PERSENT"];
                    list.Add(model);
                }
            }
            return list;
        }

         /// <summary>
        /// 根据id来获取相应的实体
        /// </summary>
        /// <param name="PercentId">id</param>
        /// <returns>返回实体</returns>
        public BoPercent GetModelById(string PercentId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from Persent where PER_ID = @PER_ID");
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@PER_ID",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = PercentId;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(),parameter);
            IList<BoPercent> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }


        /// <summary>
        /// 将table转化成相应的视图list对象表格
        /// </summary>
        /// <param name="table"></param>
        /// <returns>返回视图数据列表</returns>
        public IList<BoPercent> GetVList(DataTable table)
        {
            List<BoPercent> list = new List<BoPercent>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoPercent model = new BoPercent();
                    model.PER_ID = (string)table.Rows[i]["PER_ID"];
                    model.PER_CONTEST = table.Rows[i]["PER_CONTEST"] == System.DBNull.Value ? "" : (string)table.Rows[i]["PER_CONTEST"];
                    model.PER_AWARDS = table.Rows[i]["PER_AWARDS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["PER_AWARDS"];
                    model.PER_ORDER = (int)table.Rows[i]["PER_ORDER"];
                    model.PER_PERSENT = table.Rows[i]["PER_PERSENT"] == System.DBNull.Value ? 0 : (int)table.Rows[i]["PER_PERSENT"];
                    model.contest.CON_ID = (string)table.Rows[i]["CON_ID"];
                    model.contest.CON_COMPETITIONS = table.Rows[i]["CON_COMPETITIONS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_COMPETITIONS"];
                    model.contest.CON_CONTENTS = table.Rows[i]["CON_CONTENTS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_CONTENTS"];
                    model.contest.CON_YEAR = (Int32)table.Rows[i]["CON_YEAR"];
                    model.contest.CON_MONTH = (Int32)table.Rows[i]["CON_MONTH"];
                    model.contest.CON_ADDRESS = table.Rows[i]["CON_ADDRESS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_ADDRESS"];
                    model.contest.CON_TEACHERS = table.Rows[i]["CON_TEACHERS"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_TEACHERS"];
                    model.contest.CON_NAME = (string)table.Rows[i]["CON_NAME"];
                    model.contest.CON_STARTTIME = (DateTime)table.Rows[i]["CON_STARTTIME"];
                    model.contest.CON_ENDTIME = (DateTime)table.Rows[i]["CON_ENDTIME"];
                    model.contest.CON_CREATOR = table.Rows[i]["CON_CREATOR"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_CREATOR"];
                    model.contest.CON_EXPERIENCE = table.Rows[i]["CON_EXPERIENCE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_EXPERIENCE"];
                    model.contest.CON_IMPROVE = table.Rows[i]["CON_IMPROVE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_IMPROVE"];
                    model.contest.CON_SUMMARY = table.Rows[i]["CON_SUMMARY"] == System.DBNull.Value ? "" : (string)table.Rows[i]["CON_SUMMARY"];
                    model.contest.CON_ISCHECK = (Int32)table.Rows[i]["CON_ISCHECK"];
                    model.contest.CON_ISSHOW = (Int32)table.Rows[i]["CON_ISSHOW"];
                    model.awards.AWA_ID = table.Rows[i]["AWA_ID"] == System.DBNull.Value ? "" : (string)table.Rows[i]["AWA_ID"];
                    model.awards.AWA_NAME = table.Rows[i]["AWA_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["AWA_NAME"];
                    model.awards.AWA_TYPE = table.Rows[i]["AWA_TYPE"] == System.DBNull.Value ? 0 : (Int32)table.Rows[i]["AWA_TYPE"];
                    model.awards.AWA_LEVAL = table.Rows[i]["AWA_LEVAL"] == System.DBNull.Value ? "" : (string)table.Rows[i]["AWA_LEVAL"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 根据竞赛id来获取符合条件的个数
        /// </summary>
        /// <param name="ConId">竞赛id</param>
        /// <returns>返回个数</returns>
        public int GetCountByConId(string ConId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select count(*) from V_Percent where PER_CONTEST=@PER_CONTEST");
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@PER_CONTEST",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = ConId;
            return (int)dbhelper.ExcuteQuery(sql.ToString(),parameter).Rows[0][0];
        }


        /// <summary>
        /// 通过竞赛id来删除相应的数据
        /// </summary>
        /// <param name="ConId">竞赛id</param>
        /// <returns>返回受影响的行数</returns>
        public int DeleteByConId(string ConId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from Persent where PER_CONTEST =@PER_CONTEST");
            SqlParameter[] parameter = new SqlParameter[1]
            {
                new SqlParameter("@PER_CONTEST",SqlDbType.NVarChar,50),
            };
            parameter[0].Value = ConId;
            return dbhelper.ExcuteNoQuery(sql.ToString(),parameter);
        }
    }
}
